﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace QuizzAdminData
{
    public static class TeamDB
    {
        public static List<Team> GetTeams()
        {
            List<Team> teams = new List<Team>();

            SqlConnection conn = QuizzAdminDB.GetConnection();

            string selectStatement = "SELECT * FROM Teams";

            SqlCommand selectCmd = new SqlCommand(selectStatement, conn);

            try
            {
                conn.Open();
                SqlDataReader reader = selectCmd.ExecuteReader();
                while (reader.Read())
                {
                    Team team = new Team();

                    team.Id = (int)reader["id"];
                    team.Naam = reader["naam"].ToString();

                    teams.Add(team);
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            foreach (Team t in teams)
            {
                t.Spelers = SpelersDB.GetSpelerByTeamID(t.Id);
            }

            return teams;
        }

        public static void VoegTeamToe(string teamNaam)
        {
            SqlConnection conn = QuizzAdminDB.GetConnection();

            string sqlIns = "INSERT INTO Teams (naam) VALUES (@naam);";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.AddWithValue("@naam",teamNaam);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void VoegSpelerToe(int teamID, int spelerID)
        {
            SqlConnection conn = QuizzAdminDB.GetConnection();

            string sqlIns = "INSERT INTO Spelers_Teams (speler_id, team_id) VALUES (@speler_id, @team_id);";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.AddWithValue("@speler_id", spelerID);
                cmdIns.Parameters.AddWithValue("@team_id", teamID);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        //DB Team tabel heeft "ON DELETE CASCADE" zo wordt het team ook uit de Spelers_Teams tabel gewist met 1 enkele query
        public static void DeleteTeam(int teamID)
        {
            SqlConnection conn = QuizzAdminDB.GetConnection();

            string sql =
                "DELETE FROM Teams " +
                "WHERE Teams.id = @id";

            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", teamID);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteSpelerFromTeam(int spelerID, int teamID)
        {
            SqlConnection conn = QuizzAdminDB.GetConnection();

            string sql =
                "DELETE FROM Spelers_Teams " +
                "WHERE team_id = @team_id " +
                "AND speler_id = @speler_id";

            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@team_id", teamID);
                cmd.Parameters.AddWithValue("@speler_id", spelerID);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
